<?php
if(defined("_PARA_DBLAYER"))
    return;
define("_PARA_DBLAYER", 1);

require_once "Forum.php";
require_once "Chat.php";

class DBLayer {

    public $config        = null;
    public $db_connection = null;

    public $error_message = '';
    public $is_up         = false;

    public $latest_error = '';
    public $latest_query = '';

    public $mysql_error  = '';
    public $mysql_errno  = '';


    public function DBLayer( $config ) {

        $this->config = $config;

        $host     = $config->get('DB', 'DB_HOST');
        $username = $config->get('DB', 'DB_USERNAME');
        $password = $config->get('DB', 'DB_PASSWORD'); 
        $db_name  = $config->get('DB', 'DB_DATABASE_NAME');

        // Connect to the database (ignore errors)
        $db_conn = @mysql_connect($host, $username, $password);
        if(!$db_conn) {
            $this->error_message = "Could not connect to MySQL database";
            return;
        }

        $db_status = mysql_select_db($db_name,$db_conn);
        if( !$db_status ) {
            $this->error_message = "Could not use database $dbDataBaseName";
            return;
        }

        $this->db_connection = $db_conn;
        $this->is_up = true;
    }


    function is_up() {
        return $this->is_up;
    }


    public function now() {
        $sql = 'SELECT NOW()';
        return $this->simplequery( $sql );
    }

    public function get_forum( $topics ) {
        $threads  = $this->get_forum_threads( $topics, null, false );

        $thread_ids = array();
        foreach( $threads as $thread )
            array_push( $thread_ids, $thread->id );
        $messages = $this->get_forum_messages( $thread_ids );

        $forum = new Forum( $threads, $messages );
        return $forum;
    }

    
    public function get_forum_thread( $thread_id ) {

        $threads  = $this->get_forum_threads( null, $thread_id, false );
        if(!$threads)
            return null;
        if(sizeof($threads) != 1 ) {
            $this->latest_error = "get_forum_thread: No size in array";
            return null;
        }

        $thread   = $threads[0];
        $messages = $this->get_forum_messages( array( $thread->id ) );
        $forum    = new Forum( $threads, $messages );

        return $forum->threads[0];
        
    }


    function create_forum_thread( $topics, $screen_name, $uid, $subject, $body, $ip ) {

        $table    = $this->config->get('DB', 'DBT_FORUM_THREAD');
        $safe_topics = $this->sescape( $topics );

        $tuple    = "( null, $safe_topics, 'NORMAL', null, null, FALSE, FALSE )";
        $query = "insert into $table values $tuple";
        $query_result = $this->query( $query );

        if( !$query_result )
            return false;

        $thread_id  = mysql_insert_id();
        $message_id = $this->_create_forum_message( $thread_id, null, $screen_name, $uid, $subject, $body, $ip );

        if($message_id) {
            $this->insert_event('FORUM', 'NEW_THREAD', $thread_id, $message_id );
            return array( 'thread_id'  => $thread_id,
                          'message_id' => $message_id );
        }
        return false;
    }


    public function get_forum_threads( $topics, $id, $arch ) {

        $table    = $this->config->get('DB', 'DBT_FORUM_THREAD');

        $arch = $arch ? 'TRUE' : 'FALSE';

        if( $id && is_numeric( $id ) ) {            
            $where_clause = " where id=$id and arch_flag=$arch and del_flag=FALSE";
        }
        elseif( $topics != null ) {
            if($topics == '*')
                $where_clause = " where arch_flag=$arch and del_flag=FALSE";
            else {
                $safe_topics = $this->sescape( $topics );
                $where_clause = " where arch_flag=$arch and del_flag=FALSE and topics=$topics";
            }
        }
        else {
            $this->latest_error = "get_forum_threads got confused by params id=$id";
            return null;
        }

        $colnames = 'id, topics, state, changedate, createdate, arch_flag, del_flag';
        $query = "select $colnames from $table" . $where_clause . " order by id desc";
        $query_result = $this->query( $query );
        
        if( !$query_result )
            return null;

        $threads = array();
        while( $row = mysql_fetch_array( $query_result ) ) {
            $thread = new Thread( $row['id'],
                                  $row['topics'],
                                  $row['state'],
                                  $row['changedate'],
                                  $row['createdate'],
                                  null
                );
            array_push( $threads, $thread);
        }

        return $threads;
    }



    public function create_forum_message( $thread_id, $parent_id, $screen_name, 
                                          $uid,       $subject,   $body, $ip ) {

        $message_id = $this->_create_forum_message($thread_id, 
                                                   $parent_id,
                                                   $screen_name,
                                                   $uid,
                                                   $subject,
                                                   $body,
                                                   $ip
            );

        if(!$message_id)
            return null;

        $this->stamp_forum_thread( $thread_id);
        $this->insert_event('FORUM', 'NEW_MESSAGE', $thread_id, $message_id );
        return $message_id;

    }


    public function stamp_forum_thread( $thread_id ) {
        $table    = $this->config->get('DB', 'DBT_FORUM_THREAD');

        if(!is_numeric( $thread_id ) )
            return null;
        $query    = "update $table set changedate=now() where id=" . $thread_id;
        $query_result = $this->query( $query );
        if(!$query_result )
            return null;

        if( $this->affected_rows() == 1 )
            return true;
        return false;
    }


    private function _create_forum_message( $thread_id, $parent_id, $screen_name,
                                            $uid,       $subject,   $body, $ip ) {
        $index_table    = $this->config->get('DB', 'DBT_FORUM_INDEX');
        $body_table     = $this->config->get('DB', 'DBT_FORUM_BODIES');

        // safe values 
        $safe_tid = $thread_id ? $this->escape( $thread_id ) : 0;
        $safe_uid = $uid ? $this->escape( $uid ) : 'null';
        $safe_pid = $parent_id ? $this->escape( $parent_id ) : 0;

        $safe_screen_name = $this->sescape( $screen_name );
        $safe_subject     = $this->sescape( $subject );

        $tuple    = "( null, $safe_tid, $safe_pid, $safe_screen_name, $safe_uid, $safe_subject, null, '$ip', '', 'NORMAL', FALSE, FALSE )";
        $query = "insert into $index_table values $tuple";
        $query_result = $this->query( $query );
        if( !$query_result )
            return false;
        $message_id = mysql_insert_id();

        // insert the body
        $id = mysql_insert_id();
        $safe_body = $this->sescape( $body );
        $tuple     = "( $id, $safe_tid, $safe_body, FALSE, FALSE )";
        $query = "insert into $body_table values $tuple";
        $query_result = $this->query( $query );
        if(!$query_result)
            return false;

        return $message_id;
    }


    // archive old threads
    public function forum_cleanup() {

        $th_table = $this->config->get('DB', 'DBT_FORUM_THREAD');        
        $ix_table = $this->config->get('DB', 'DBT_FORUM_INDEX');
        $bd_table = $this->config->get('DB', 'DBT_FORUM_BODIES'); 

        $max_count = $this->config->get('FORUM', 'ACTIVE_THREAD_COUNT');

        // find oldest id to keep
        $query_1 = 
            "select id from $th_table where arch_flag=FALSE and del_flag=FALSE " . 
            "order by id desc limit $max_count, 1";
        
        $query_result = $this->query( $query_1 );

        if(!$query_result)
            return;

        $last_id = 0;
        if( mysql_num_rows( $query_result ) == 1 ) {
            $row = mysql_fetch_array( $query_result );
            $last_id = $row['id'];
        }

        $wclause = "and arch_flag=FALSE";
        $q1 = "update $th_table set arch_flag=TRUE where id<=$last_id $wclause";
        $q2 = "update $ix_table set arch_flag=TRUE where thread_id<=$last_id $wclause";
        $q3 = "update $bd_table set arch_flag=TRUE where thread_id<=$last_id $wclause";

        $r1 = $this->query( $q1 );
        $r2 = $this->query( $q2 );
        $r3 = $this->query( $q3 );

        // FIXME: shall delete orphans //
    }


    public function forum_delete_message_and_children( $thread_id, $message_id ) {
        $retval = $this->_forum_change_message_and_children_delflag( $thread_id, $message_id, 'TRUE' );
        $this->insert_event('FORUM', 'DEL_MESSAGE', $thread_id, $message_id );        
        return $retval;
    }


    public function forum_undelete_message_and_children( $thread_id, $message_id ) {
        $retval = $this->_forum_change_message_and_children_delflag( $thread_id, $message_id, 'FALSE' );
        $this->insert_event('FORUM', 'UNDEL_MESSAGE', $thread_id, $message_id );
        return $retval;
    }


    // mark message and it's children as deleted
    private function _forum_change_message_and_children_delflag( $thread_id, $message_id, $delflag ) {

        $thread = $this->get_forum_thread( $thread_id );
        if( !$thread )
            return false;

        $message     = $thread->find_message( $message_id );
        if( !$message )
            return false;

        $ix_table      = $this->config->get('DB', 'DBT_FORUM_INDEX' );
        $bd_table      = $this->config->get('DB', 'DBT_FORUM_BODIES');

        $message_ids = $message->get_tree_ids();
        $in_clause = "(" . join( ',', $message_ids ) . ")";

        $q1 = "update $bd_table set del_flag=$delflag where id in $in_clause";
        $q2 = "update $ix_table set del_flag=$delflag where id in $in_clause";

        $this->query( $q1 );
        $this->query( $q2 );
        $retval = $this->affected_rows();

        return $retval;
    }


    // mark thread as deleted
    public function forum_delete_threads( $thread_ids ) {
        $retval = $this->_forum_threads_change_delflag( $thread_ids, 'TRUE');
        $this->insert_event('FORUM', 'UNDEL_MESSAGE', $thread_id, $message_id );
        return $retval;
    }


    // mark thread as deleted
    public function forum_undelete_threads( $thread_ids ) {
        $retval = $this->_forum_threads_change_delflag( $thread_ids, 'FALSE');
        $this->insert_event('FORUM', 'UNDEL_MESSAGE', $thread_id, $message_id );
        return $retval;
    }


    private function _forum_threads_change_delflag( $thread_ids, $flag ) {
        if( is_numeric( $thread_ids ) ) {
            $thread_ids = array( $thread_ids );
        }
        elseif( !is_array( $thread_ids ) )
            return -1;

        $th_table      = $this->config->get('DB', 'DBT_FORUM_THREAD' );
        $ix_table      = $this->config->get('DB', 'DBT_FORUM_INDEX' );
        $bd_table      = $this->config->get('DB', 'DBT_FORUM_BODIES');

        $retval = 0;
        foreach( $thread_ids as $id ) {
            $q1 = "update $bd_table set del_flag=$flag where thread_id=$id";
            $q2 = "update $ix_table set del_flag=$flag where thread_id=$id";
            $q3 = "update $th_table set del_flag=$flag where id=$id";
       
            $r1 = $this->query( $q1 );
            $r2 = $this->query( $q2 );
            $retval += $this->affected_rows();

            $r3 = $this->query( $q3 );
        }

        return $retval;
    }


    public function forum_archive_threads( $thread_ids ) {
        return $this->_forum_threads_change_arch( $thread_ids, 'TRUE' );
    }


    public function forum_unarchive_threads( $thread_ids ) {
        return $this->_forum_threads_change_arch( $thread_ids, 'FALSE' );
    }


    // archive one or more threads
    private function _forum_threads_change_arch( $thread_ids, $arch ) {
        if( is_numeric( $thread_ids ) ) {
            $thread_ids = array( $thread_ids );
        }
        elseif( !is_array( $thread_ids ) )
            return null;

        $th_table      = $this->config->get('DB', 'DBT_FORUM_THREAD' );
        $ix_table      = $this->config->get('DB', 'DBT_FORUM_INDEX' );
        $bd_table      = $this->config->get('DB', 'DBT_FORUM_BODIES');

        $retval = -1;

        foreach( $thread_ids as $id ) {

            $q1 = "update $th_table set arch_flag=$arch where id=$id";
            $q2 = "update $ix_table set arch_flag=$arch where thread_id=$id";
            $q3 = "update $bd_table set arch_flag=$arch where thread_id=$id";
       
            $r1 = $this->query( $q1 );
            $r2 = $this->query( $q2 );
            $retval = $this->affected_rows();

            $r3 = $this->query( $q3 );
        }

        return $retval;

    }


    public function get_forum_message( $id ) {
        $table = $this->config->get('DB', 'DBT_FORUM_INDEX');
        if(!is_numeric( $id ))
            return null;
    
        $query = "select * from $table where id=$id and del_flag=FALSE";
        $query_result = $this->query( $query );
        if(!$query_result) {
            $this->latest_error = 'Fetch message failed';
            return null;
        }
    
        if(mysql_num_rows( $query_result ) != 1) {
            $this->latest_error = 'Message not found:' . mysql_num_rows( $query_result );
            return null;
            }

        $row = mysql_fetch_array($query_result);

        return $this->forum_row_to_msg( $row );
    }


    // fetch messages in the database
    public function get_forum_messages( $thread_ids ) {
        $table = $this->config->get('DB', 'DBT_FORUM_INDEX');

        if($thread_ids) {
            $where_clause = 
                " where del_flag=FALSE and " . 
                "thread_id in (" . 
                join( ',', $thread_ids ) . 
                ")";
        }
        else
            $where_clause = '';

        $query = "select * from $table". $where_clause. "order by id asc;";

        $query_result = $this->query( $query );
        if(!$query_result) {
            $this->latest_error = 'Fetch message index failed';
            return null;
        }

        $messages = array();
        while( $row = mysql_fetch_array($query_result) ) {
            $message = $this->forum_row_to_msg( $row );
            array_push( $messages, $message );
        }

        return $messages;
    }


    public function get_forum_message_bodies( $id_or_ids ) {
        $table = $this->config->get('DB', 'DBT_FORUM_BODIES');
        
        $return_array = false;

        if( is_numeric( $id_or_ids ) ) 
            $query = "select id, body from $table where id=$id_or_ids";
        elseif( is_array( $id_or_ids) ) {
            $query = 
            "select id, body from $table where id in (".
            implode( ',', $id_or_ids ) . ")";
            $return_array = true;
        }
        else {
            $this->latest_error = "get_message_bodies got confused by params";
            return null;
        }

        $query_result = $this->query( $query );
        if(!$query_result) {
            $this->latest_error = 'Fetch message index failed';
            return null;
        }


        if($return_array) {
            $messages = array();
            while( $row = mysql_fetch_array( $query_result ) ) {
                $message = new ForumMessageBody( $row['id'], $row['body'] );
                array_push( $messages, $message);
            }

            return $messages;

        }

        else {
            if(mysql_num_rows( $query_result ) != 1) {
                $this->latest_error = 'Messagebody not found:' . mysql_num_rows( $query_result );
                return null;
            }

            $row = mysql_fetch_array( $query_result );
            $message = new ForumMessageBody( $row['id'], $row['body'] );

            return $message;
        }
    }


    private function forum_row_to_msg( $row ) {
        return
            new ForumMessage( $row['id'], 
                              $row['parent_id'], 
                              $row['thread_id'],
                              $row['screen_name'],
                              $row['uid'],
                              $row['subject'],
                              $row['date'],
                              $row['tags'],
                              $row['xtras']
                );
    }


    public function get_chat_messages( $since_id ) {
        $table    = $this->config->get('DB', 'DBT_CHAT');
        $since_id = $this->escape( $since_id );
        $query = "select * from $table where id>$since_id and status=''" . 
                 "order by id desc limit " . 
                  $this->config->get('CHAT', 'MAX_SHOUT_COUNT') ;

        $query_result = $this->query( $query );
        if( !$query_result ) {
            $this->latest_error = 'Fetch chat messages failed';
            return null;
        }

        $messages = array ();
        while ($row = mysql_fetch_array( $query_result ) ) {
            $message = new ChatMessage( $row['id'], 
                                        $row['screen_name'],
                                        $row['uid'],
                                        $row['text'],
                                        $row['time'],
                                        $row['ip']
                );
            array_push( $messages, $message);
        }

        return $messages;
    }


    public function get_chat() {
        $messages = $this->get_chat_messages( 0 );
        $chat     = new Chat( $messages );
        return $chat;
    }


    public function create_chat_message( $screen_name, $uid, $text, $ip ) {

        $table    = $this->config->get('DB', 'DBT_CHAT');

        // safe values 
        $safe_screen_name = $this->sescape( $screen_name );

        if($uid)
            $safe_uid         = $this->escape( $uid );
        else
            $safe_uid         = 'null';

        $safe_text        = $this->sescape( $text );

        $tuple    = "( null, $safe_screen_name, $safe_uid, $safe_text, null, '', '$ip')";
        $query = "insert into $table values $tuple";


        $query_result = $this->query( $query );
        if(!$query_result) {
            $this->latest_error = 'Insert chat_message failed' . mysql_error();
            return 'error';
        }        

        $this->chat_cleanup();

        $this->insert_event('CHAT', 'NEW_MESSAGE', null, null );

        return 'ok';
    }


    public function del_chat_messages( $ids ) {
        $table    = $this->config->get('DB', 'DBT_CHAT');

        foreach($ids as $id) {
            $id = $this->escape( $id );
            $query = "update $table set status='DEL' where id=$id";
            $query_result = $this->query( $query );
            if(!$query_result) {
                return;
            }
        }

        $this->insert_event('CHAT', 'DEL_MESSAGE', null, null );

        return 'ok';
    }


    public function del_all_chat_messages(  ) {
        $table    = $this->config->get('DB', 'DBT_CHAT');


        $query = "update $table set status='DEL'";
        $query_result = $this->query( $query );
        if( !$query_result ) {
            return;
        }

        $this->insert_event('CHAT', 'DEL_MESSAGE', null, null );

        return 'ok';
    }


    public function chat_cleanup( ) {
        $table      = $this->config->get('DB', 'DBT_CHAT');
        $table_arch = $this->config->get('DB', 'DBT_CHAT_ARCH'); 

        $max_count = $this->config->get('CHAT', 'MAX_SHOUT_COUNT');

        // find oldest id to keep
        $query_1 = "select id from $table where status='' order by id desc limit $max_count, 1";
        
        $query_result = $this->query( $query_1 );
        $last_id = 0;
        if( mysql_num_rows( $query_result ) == 1 ) {
            $row = mysql_fetch_array( $query_result );
            $last_id = $row['id'];
        }
        else return;

        // select the rows to be moved to archive
        $query_2 = "select * from $table where status!='' or id<=$last_id";
        $query_result = $this->query( $query_2 );

        // Insert into archive
        $values = array();
        while($row = mysql_fetch_array( $query_result )) {
            $a_id          = 'NULL';
            $a_screen_name = $this->escape( $row['screen_name'] );
            $a_uid         = $row['uid'] ? $row['uid']: 'NULL';
            $a_text        = $this->escape( $row['text'] );
            $a_time        = $row['time'];
            $a_status      = $row['status'];
            $a_ip          = $row['ip'];

            $value = "($a_id, '$a_screen_name', $a_uid, '$a_text', '$a_time', '$a_status', '$a_ip')";
            array_push( $values, $value );
        }

        $query_3 = "insert into $table_arch values " . implode  ( ','  , $values );
        $query_result = $this->query( $query_3 );

        // select the rows to be moved to archive
        $query_4 = "delete from $table where status!='' or id<=$last_id";
        $query_result = $this->query( $query_4 );

        // FIXME FIXME: id will be reset to 0 if all shouts are deleted
        // 1. check if there are zero messages 
    }

    
    //#### Linkroll functions ########################################

        
    private function _get_linkroll_categories( $admin ) {
        $table    = $this->config->get('DB', 'DBT_LINKROLL_CATEGORY');

        $where_clause = '';
        if(!$admin)
            $where_clause = 'where active=TRUE';

        $query = "select * from $table $where_clause " . 
                 "order by id asc";

        $query_result = $this->query( $query );
        if( !$query_result ) {
            $this->latest_error = 'Fetch linkroll categories failed';
            return null;
        }

        $cats = array ();
        while ($row = mysql_fetch_array( $query_result ) ) {
            $cat = new LinkRollCategory( $row['id'], 
                                         $row['name'],
                                         $row['active']
                );
            array_push( $cats, $cat);
        }

        return $cats;

    }


    public function get_linkroll_categories( ) {
        return $this->_get_linkroll_categories( 0 );
    }


    public function get_linkroll_categories_for_admin( ) {
        return $this->_get_linkroll_categories( 1 );
    }


    public function get_linkroll_category( $id ) {
        $table    = $this->config->get('DB', 'DBT_LINKROLL_CATEGORY');

        $sf_id = $this->escape( $id );

        $query = "select id, name, active from $table where id=$id ";

        $query_result = $this->query( $query );
        if( !$query_result ) {
            $this->latest_error = 'Fetch linkroll category failed';
            return null;
        }

        if ($row = mysql_fetch_array( $query_result ) ) {
            $cat = new LinkRollCategory( $row['id'], 
                                         $row['name'],
                                         $row['active']
                );
            return $cat;
        }

        return null;
    }


    public function create_linkroll_category( $name, $active) {
        $table    = $this->config->get('DB', 'DBT_LINKROLL_CATEGORY');

        // safe values 
        $sf_name     = $this->sescape( $name );
        $sf_active   = $active ? 'TRUE' : 'FALSE';

        $tuple    = "($sf_name, $sf_active )";
        $query = "insert into $table (name, active) value $tuple";

        $query_result = $this->query( $query );
        if(!$query_result) {
            $this->latest_error = 'Insert linkroll_link failed :' . mysql_error();
            return 'error' . $this->latest_error;
        }

        return 'ok';
    }


    public function save_linkroll_category( $id, $name, $active) {
        $table    = $this->config->get('DB', 'DBT_LINKROLL_CATEGORY');

        // safe values 
        $sf_id       = $this->escape( $id );
        $set = array();
        $set['name']     = $this->sescape( $name );
        $set['active']   = $active ? 'TRUE' : 'FALSE';

        $query = "update $table set " . $this->format_sets( $set ) . " where id=$id";
        $query_result = $this->query( $query );

        if(!$query_result)
            return 'error:db:';
        if($this->affected_rows() == 1)
            return 'ok';
        return 'error:not_found';

    }

    // what to do with links with this id ???????
    public function delete_linkroll_category( $id ) {
        $table    = $this->config->get('DB', 'DBT_LINKROLL_CATEGORY');
        $query    = "delete from $table where id=$sf_id";

        $query_result = $this->query( $query );

        if(!$query_result)
            return -1;
        return $this->affected_rows();
    }


    private function linkroll_row_to_link( $row ) {
        $link = new LinkRollLink( $row['id'], 
                                  $row['url'],
                                  $row['headline'],
                                  $row['subby_screen_name'],
                                  $row['subby_uid'],
                                  $row['category_id'],
                                  $row['time'],
                                  $row['approved'],
                                  $row['ip']
            );
        return $link;
    }


    private function get_linkroll_links( $limit, $all ) {

        $colnames = 'id, url, headline, subby_screen_name, subby_uid, category_id, time, approved, ip';
        
        $table    = $this->config->get('DB', 'DBT_LINKROLL');
        if( $all )
            $query = "select $colnames from $table " .
                "order by id desc limit $limit";
        else
            $query = "select $colnames from $table where approved=TRUE " . 
                "order by id desc limit $limit";

        $query_result = $this->query( $query );
        if( !$query_result ) {
            $this->latest_error = 'Fetch linkroll messages failed';
            return null;
        }

        $links = array ();
        while ($row = mysql_fetch_array( $query_result ) ) {
            $link = $this->linkroll_row_to_link( $row );
            array_push( $links, $link);
        }

        return $links;        
    }


    public function get_linkroll() {
        $links      = $this->get_linkroll_links( 100, false );
        $categories = $this->get_linkroll_categories();
        $linkroll = new Linkroll( $links, $categories );
        return $linkroll;
    }


    public function get_linkroll_for_admin() {
        $links      = $this->get_linkroll_links( 1000000, true );
        $categories = $this->get_linkroll_categories();
        $linkroll = new Linkroll( $links, $categories );
        return $linkroll;
    }


    public function get_linkroll_link( $id ) {
        $table    = $this->config->get('DB', 'DBT_LINKROLL');

        $sid   = $this->escape( $id );
        $query = "select * from $table where id=$sid";

        $query_result = $this->query( $query );
        if( !$query_result ) {
            $this->latest_error = 'Fetch linkroll link failed';
            return null;
        }

        if ($row = mysql_fetch_array( $query_result ) )
            return $this->linkroll_row_to_link( $row );

        return null;
        
        
    }


    public function create_linkroll_link( $url, $headline, $subby_screen_name, 
                                          $subby_uid, $category_id, $approved, $ip ) {

        $table    = $this->config->get('DB', 'DBT_LINKROLL');

        // safe values 
        $sf_url      = $this->sescape( $url );
        $sf_headline = $this->sescape( $headline );

        $sf_subby_screen_name = $this->sescape( $subby_screen_name );
        $sf_approved = $approved ? 'TRUE' : 'FALSE';

        if($subby_uid)
            $sf_subby_uid     = $this->escape( $subby_uid );
        else
            $sf_subby_uid     = 'null';

        if($category_id)
            $sf_category_id   = $this->escape( $category_id );
        else
            $sf_category_id   = 1;


        $tuple    = "( null, $sf_url, $sf_headline, $sf_subby_screen_name, $sf_subby_uid, $sf_category_id, null, $sf_approved, '$ip')";
        $query = "insert into $table values $tuple";

        $query_result = $this->query( $query );
        if(!$query_result) {
            $this->latest_error = 'Insert linkroll_link failed :' . mysql_error();
            return 'error';
        }

        return 'ok';
    }


    public function save_linkroll_link( $link ) {
        $table = $this->config->get('DB', 'DBT_LINKROLL');

        // safe values 
        $set['url']               = $this->sescape( $link->url );
        $set['headline']          = $this->sescape( $link->headline );
        $set['subby_screen_name'] = $this->sescape( $link->submitter );
        $set['subby_uid']         = $this->escape( $link->submitter_id );
        $set['category_id']       = $this->escape( $link->category_id );
        $set['approved']          = $link->approved ? 'TRUE' : 'FALSE';

        $query = "update $table set " . $this->format_sets( $set ) . " where id=$link->id";
        $query_result = $this->query( $query );

        if(!$query_result)
            return 'error:db:';
        if($this->affected_rows() == 1)
            return 'ok';
        return 'error:not_found';
    }


    private function linkroll_do_stuff_to_links( $id_or_ids, $operation ) {
        $table    = $this->config->get('DB', 'DBT_LINKROLL');

        if(is_numeric( $id_or_ids ))
           $link_ids = array( $id_or_ids );
        elseif (is_array( $id_or_ids ) )
            $link_ids = $id_or_ids;
        else return 'error';

        $in_clause = "(" . join( ',', $link_ids ) . ")";

        if( $operation == 'APPROVE' )
            $query = "update $table set approved=TRUE where id in $in_clause";
        elseif( $operation == 'DELETE' )
            $query = "delete from $table where id in $in_clause";        
        else
            return 'error';

        $this->query( $query );
        $rows = $this->affected_rows();

        return 'ok';
    }

    
    public function linkroll_approve_links( $id_or_ids) {
        $retval = $this->linkroll_do_stuff_to_links( $id_or_ids, 'APPROVE' );

        // insert the event into event table
        $rows = $this->affected_rows();
        if( ($retval == 'ok')) {
            if ( $rows )
                $this->insert_event('LINKROLL', 'APPROVED', null, null );
            return $rows;
        }

        return -1;
    }


    public function linkroll_delete_links( $id_or_ids ) {
        $retval = $this->linkroll_do_stuff_to_links( $id_or_ids, 'DELETE' );

        // insert the event into event table
        $rows = $this->affected_rows();
        if(( $retval == 'ok')) {

            if( $rows )
                $this->insert_event('LINKROLL', 'DELETED', null, null );
            return $rows;
        }

        return -1;
    }

    #### ip block functions #####################################

    public function get_ipblocks() {
        $table    = $this->config->get('DB', 'DBT_IPBLOCKS');

        $columns = 'id, name, createdate, definition, message, block_logged_in, block_viewing, active';
        $query   = "select $columns from $table order by id desc;";

        $query_result = $this->query( $query );
        if(!$query_result) {
            $this->latest_error = 'get_ipblocks failed';
            return null;
        }

        $blocks = array();
        while( $row = mysql_fetch_array($query_result) ) {
            $block = $this->row_to_ipblock( $row );
            array_push( $blocks, $block );
        }

        return $blocks;
    }


    public function get_ipblock_by_id( $id ) {
        $table    = $this->config->get('DB', 'DBT_IPBLOCKS');

        $columns = 'id, name, createdate, definition, message, block_logged_in, block_viewing, active';
        $query   = "select $columns from $table where id=$id";

        $query_result = $this->query( $query );
        if(!$query_result) {
            $this->latest_error = 'Fetch ipblock failed';
            return null;
        }

        $block = null;
        if( $row = mysql_fetch_array($query_result) ) {
            $block = $this->row_to_ipblock( $row );

        }

        return $block;
    }


    // create new ipblock in db or update an existing one
    public function save_ipblock( $block ) {
        $table    = $this->config->get('DB', 'DBT_IPBLOCKS');
        
        #### safe values ########################################
        $sets = array(
            'name'            =>  $this->sescape( $block->name ),
            'createdate'      =>  'NULL',
            'definition'      =>  $this->sescape( $block->definition ),
            'message'         =>  $this->sescape( $block->message ),
            'block_logged_in' =>  $this->escape( $block->block_logged_in ),
            'block_viewing'   =>  $this->escape( $block->block_viewing ),
            'active'          =>  $this->escape( $block->active )
            );
        if( $block->id ) {
            $query = "update $table set " . $this->format_sets( $sets ) . " where id=$block->id";
            $qres  = $this->updatequery( $query );
        }
        else {
            $query = $this->format_insert_query( $table, $sets );
            $qres = $this->query( $query );
            if( !$qres )
                return null;
            $block->id  = mysql_insert_id();
        }

        return $block;
    }

    public function delete_ipblock( $id ) {
        $table    = $this->config->get('DB', 'DBT_IPBLOCKS');
        $sql = "delete from $table where id=$id";
        $query_result = $this->query( $sql );
        return $this->affected_rows();
    }


    private function row_to_ipblock( $row ) {
        return new IPBlock(
            $row['id'],
            $row['name'],
            $row['date'],
            $row['definition'],
            $row['message'],
            $row['block_logged_in'],
            $row['block_viewing'],
            $row['active']
            );
    }


    #### Quotes related functions ########################################
    public function get_quote_by_id( $id ) {
        $table    = $this->config->get( 'DB', 'DBT_QUOTES' );

        $columns = 'id, quote, name, createdate, active';
        $query   = "select $columns from $table where id=$id";

        $query_result = $this->query( $query );
        if(!$query_result) {
            $this->latest_error = 'Fetch quote failed';
            return null;
        }

        $quote = null;
        if( $row = mysql_fetch_array($query_result) ) {
            $quote = $this->row_to_quote( $row );

        }

        return $quote;
    }


    private function _get_quotes( $pagenum, $pagesize, $active ) {
        $table    = $this->config->get( 'DB', 'DBT_QUOTES' );

        $where   = $active ? " WHERE active=TRUE" : "";
        $limit   = $this->get_limit( $pagenum, $pagesize );
        $columns = 'id, quote, name, createdate, active';
        $query   = "select $columns from $table $where order by id desc $limit;";

        $query_result = $this->query( $query );
        if(!$query_result) {
            $this->latest_error = 'get_quotes failed';
            return null;
        }

        $quotes = array();
        while( $row = mysql_fetch_array($query_result) ) {
            $quote = $this->row_to_quote( $row );
            array_push( $quotes, $quote );
        }

        return $quotes;
    }

    public function get_latest_quotes( $count ) {
        return $this->_get_quotes( 1, $count, 1 );
    }

    public function get_quotes( $pagenum, $pagesize ) {
        return $this->_get_quotes( $pagenum, $pagesize, 0 );
    }

    private function row_to_quote( $row ) {
        return new Quote(
            $row['id'],
            $row['quote'],
            $row['name'],
            $row['createdate'],
            $row['active']
            );
    }


    // create new quotek in db or update an existing one
    public function save_quote( $quote ) {
        $table    = $this->config->get('DB', 'DBT_QUOTES');
        
        #### safe values ########################################
        $sets = array(
            'quote'           =>  $this->sescape( $quote->quote ),
            'name'            =>  $this->sescape( $quote->name ),
            'createdate'      =>  'NULL',
            'active'          =>  $this->escape( $quote->active )
            );

        if( $quote->id ) {
            $query = "update $table set " . $this->format_sets( $sets ) . " where id=$quote->id";
            $qres  = $this->updatequery( $query );
        }
        else {
            $query = $this->format_insert_query( $table, $sets );
            $qres = $this->query( $query );
            if( !$qres )
                return null;
            $quote->id  = mysql_insert_id();
        }

        return $quote;
    }

    public function delete_quote( $id ) {
        $table    = $this->config->get('DB', 'DBT_QUOTES');
        $sql = "delete from $table where id=$id";
        $query_result = $this->query( $sql );
        return $this->affected_rows();
    }



    #### event functions ########################################
    public  function insert_event( $section, $event, $meta1, $meta2) {
        $table = $this->config->get('DB', 'DBT_EVENTS');

        if($meta1 != null) { 
            $meta1 = "'" . $this->escape( $meta1 ) ."'";
        }
        else $meta1 = 'null';

        if($meta2 != null) { 
            $meta2 = "'" . $this->escape( $meta2 ) ."'";
        }
        else $meta2 = 'null';

        $query = "insert into $table values ( null, null, '$section', '$event', $meta1, $meta2 )";
        $query_result = $this->query( $query );

        // cleanup event tables every once in a while
        if($this->one_in( 1000 ))
            $this->clean_events();
    }


    public function clean_events() {
        $table = $this->config->get('DB', 'DBT_EVENTS');

        // Keep 20 newest events
        $query = "select ev_id from $table order by ev_id desc limit 20,1";
        $id    = $this->simplequery( $query );

        if($id != null) {
            $this->query("delete from $table where ev_id<=$id");
        }
    }


    public function get_events( $since ) {
        $table = $this->config->get('DB', 'DBT_EVENTS');

        $query = "select * from $table where ev_id>$since order by ev_id desc limit 10";
        $query_result = $this->query( $query );

        $events = array();
        while( $row = mysql_fetch_array($query_result) ) {
            $event = new Event( $row['ev_id'], 
                                $row['ev_date'], 
                                $row['section'],
                                $row['ev_name'],
                                $row['meta1'],
                                $row['meta2']
                );
            array_push( $events, $event);
        }

        return $events;

    }


    public function get_newest_event(  ) {
        $table = $this->config->get('DB', 'DBT_EVENTS');

        $query = "select * from $table order by ev_id desc limit 1";
        $query_result = $this->query( $query );

        $row = mysql_fetch_array( $query_result );
        if( !$row )
            return null;

        $event = new Event( $row['ev_id'], 
                            $row['ev_date'], 
                            $row['section'],
                            $row['ev_name'],
                            $row['meta1'],
                            $row['meta2']
            );

        return $event;

    }


    // online account registration...
    public function account_register( $screen_name, $email, $pwd_md5 ) {
        $act_code = md5( $screen_name . $email . $pwd_md5 . time());
        $state      = 'CREATED';
        $group_name = 'user';

        return $this->account_create( $screen_name, $email, $pwd_md5, $state, $group_name, $act_code );
    }


    public function account_confirm( $uid, $act_code ) {
        // noget med account_fetch_by_uid
        if(!is_numeric( $uid ))
            return 'param:' . $uid ;

        $account = $this->account_fetch_by_uid( $uid );
        if( !$account )
            return 'not_found';
        if( $account->act_code != $act_code )
            return 'act_code';

        $table = $this->config->get('DB', 'DBT_ACCOUNT');

        $query    = "update $table set state='ACTIVE' where uid=" . $uid;
        $query_result = $this->query( $query );
        if(!$query_result )
            return null;

        $state = 'ACTIVE';

        return 'ok';
    }


    public function account_create( $screen_name, $email,      $pwd_md5, 
                                    $state,       $group_name, $act_code ) {
        $table = $this->config->get('DB', 'DBT_ACCOUNT');

        if($this->account_fetch_by_email( $email ))
            return 'dup_email';
        if($this->account_fetch_by_screenname( $screen_name ))
            return 'dup_screen_name';


        $s_email       = $this->sescape( strtolower( $email ) );
        $s_screen_name = $this->sescape( $screen_name );
        $s_pwd_md5     = $this->sescape( $pwd_md5 );

        $s_state       = $this->sescape( $state );
        $s_group_name  = $this->sescape( $group_name );
        $s_act_code    = $this->sescape( $act_code );

        $query = "insert into $table values " . 
            "( null, $s_screen_name, $s_pwd_md5, $s_email, $s_state, $s_group_name, 0, 0, 'User created', null, null, $s_act_code )";

        $query_result = $this->query( $query );
        if( !$query_result ) {
            if( $this->mysql_errno = 1062 )
                return 'dup';
            return 'query';
        }

        $uid = mysql_insert_id();

        $account = $this->account_fetch_by_uid( $uid );
        return $account;

    }

    public function account_profile_create() {
    }


    public function account_save( $acc ) {
        $table = $this->config->get('DB', 'DBT_ACCOUNT');

        $sets = array();
        $sets['screen_name'] = $this->sescape( $acc->screen_name );
        $sets['pwd_md5']     = $this->sescape( $acc->pwd_md5 );
        $sets['email']       = $this->sescape( $acc->email );
        $sets['state']       = $this->sescape( $acc->state );
        $sets['group_name']  = $this->sescape( $acc->group_name );
        $sets['msg_count']   = $acc->msg_count;
        $sets['karma']       = $acc->karma;
        $sets['comment']     = $this->sescape( $acc->comment );

        $query = "update $table set " . $this->format_sets( $sets ) . " where uid=$acc->uid";
        $query_result = $this->query( $query );

        return $query_result ? true : false;

    }


    public function account_fetch_by_email( $email ) {
        $email = $this->escape( $email );
        $table = $this->config->get('DB', 'DBT_ACCOUNT');

        $query = "select * from $table where email='$email'";
        $query_result = $this->query( $query );

        return $this->account_from_result( $query_result );
    }


    public function account_fetch_by_screenname( $screenname ) {
        $screenname = $this->escape( $screenname );
        $table = $this->config->get('DB', 'DBT_ACCOUNT');

        $query = "select * from $table where screen_name='$screenname'";
        $query_result = $this->query( $query );

        return $this->account_from_result( $query_result );
    }



    public function account_fetch_by_uid( $uid ) {

        $uid   = $this->escape( $uid );
        $table = $this->config->get('DB', 'DBT_ACCOUNT');


        $query = "select * from $table where uid='$uid'";
        $query_result = $this->query( $query );

        return $this->account_from_result( $query_result );
    }


    public function account_search_by_screenname( $screenname ) {
        $screenname = $this->escape( $screenname );
        $table = $this->config->get('DB', 'DBT_ACCOUNT');

        $query = "select * from $table where screen_name like '%$screenname%'";
        $query_result = $this->query( $query );

        return $this->accounts_from_result( $query_result );
    } 


    public function account_fetch_multiple( $state ) {

        $table = $this->config->get('DB', 'DBT_ACCOUNT');

        $query = "select * from $table where status='$state'";
        $query_result = $this->query( $query );

        return $this->account_from_result( $query_result );
    }


    public function account_delete_forever( $uid ) {
        $table = $this->config->get('DB', 'DBT_ACCOUNT');

        $uid = $this->escape( $uid );

        $query = "delete from $table where uid=$uid";
        $query_result = $this->query( $query );

    }


    function check_tables() {
        global $PARA_TABLES;

        $prefix = $this->config->get('DB', 'DB_PREFIX');

        $table_names  = array_keys( $PARA_TABLES );
        $query_result = $this->query( "show tables" );

        if(mysql_num_rows( $query_result ) < sizeof( $table_names ) )
            return 0;

        $found_names = array();
        while( $row = mysql_fetch_row( $query_result ) ) {
            array_push( $found_names, $row[0]);
        }
    
        foreach($table_names as $tname) {
            $table_name = $prefix . $tname;
            if(!in_array( $table_name, $found_names ))
                return 0;
        }

        return 1;
    }


    function drop_tables( $force ) {
        global $PARA_TABLES;

        $prefix = $this->config->get('DB', 'DB_PREFIX');

        $success = true;
        foreach($PARA_TABLES as $tname => $create_text ) {
            $fullname = $prefix . $tname;
            $query = "drop table $fullname";

            $query_result = $this->query( $query );
            if( !$query_result && !$force ) {
                $success = false;
                if( !$force )
                    throw new Exception("drop_tables: table $fullname gives " . mysql_error());
            }
        }

        return $success;

    }


    public function create_tables( $force ) {
        global $PARA_TABLES;

        $prefix = $this->config->get('DB', 'DB_PREFIX');

        $success = true;

        foreach($PARA_TABLES as $tname => $create_text ) {
            $fullname = $prefix . $tname;
            $query = "create table $fullname $create_text";

            $query_result = $this->query( $query );
            if( !$query_result ) {
                $success = false;
                if( !$force )
                    throw new Exception("create_tables: table $fullname gives " . mysql_error());
            }
        }

        return $success;
    }


    public function insert_defaults() {
        global $PARA_CONFIG;
        global $PARA_DEFAULTS;

        foreach($PARA_DEFAULTS as $tname => $values ) {
            $fullname = $PARA_CONFIG['DB_PREFIX'] . $tname;
            if(is_string( $values ) ) {
                $query = "insert into $fullname values $values";
                $query_result = $this->query( $query );
                if(!$query_result) {
                    throw new Exception("insert_defaults(1) table: $fullname gives " . mysql_error());
                }
            }
            elseif( is_array( $values ) ) {
                foreach($values as $cur_value) {
                    $query = "insert into $fullname values $cur_value";
                    $query_result = $this->query( $query );
                    if(!$query_result) {
                        throw new Exception("insert_defaults(2) table: $fullname gives " . mysql_error());
                    }
                }
            }
        }
    }


    public function read_config() {
        $config = $this->config;
        $table  = $this->config->get('DB', 'DBT_CONFIG');

        $query  = "select type, name, value from $table";
        $query_result = $this->query( $query );
        if(!$query_result) {
            throw new Exception("read_config failed: " . mysql_error());
        }
        
        while( $row = mysql_fetch_array($query_result) ) {
            $config->set($row[0], $row[1], $row[2]);
        }
    }


    public function save_config_value( $section, $key, $value ) {
        $config = $this->config;
        $table  = $this->config->get('DB', 'DBT_CONFIG');

        if($value == null)
            $value = 'NULL';
        else
            $value = "'" . $this->escape( $value ) . "'";

        $query  = "update $table set value=$value where type='$section' and name='$key'";
        $query_result = $this->query( $query );


    }

    // Santa's little helpers ////////////////////////////////////////

    private function escape( $something ) {
        return mysql_escape_string( $something );
    }


    private function sescape( $something ) {
        return "'" . mysql_escape_string( $something ) . "'";
    }


    private function query( $sql ) {
        $this->latest_query = $sql;
        $this->mysql_error  = 'None';
        $this->mysql_errno  = 'None';
        $query_result = mysql_query( $sql, $this->db_connection );

        if( !$query_result ) {
            $this->mysql_error = mysql_error( $this->db_connection );
            $this->mysql_errno = mysql_errno( $this->db_connection );
        }
        return $query_result;
    }


    private function affected_rows() {
        if(!$this->db_connection)
            return -1;
        return mysql_affected_rows( $this->db_connection );

    }


    private function simplequery( $sql ) {
        $this->latest_query = $sql;
        $query_result = mysql_query( $sql, $this->db_connection );
        if( $query_result ) {
            if( mysql_num_rows($query_result) > 0 )
                return mysql_result( $query_result, 0 );
            return null;
        }
        else
            return null;
    }


    private function updatequery( $sql ) {
        $qres = $this->query( $sql );
        if(!$qres)
            return -1;

        return $this->affected_rows();
    }


    private function account_from_result( $query_result ) {
        $res = $this->accounts_from_result( $query_result );

        if($res == null)
            return null;

        if( sizeof( $res ) == 1 ) {
            $res[0]->is_guest = false;
            return $res[0];
        }

        return $res;

    }


    private function accounts_from_result( $query_result ) {
        $this->latest_error = "no error";

        if( !$query_result ) {
            $this->latest_error = 'User fetch error: ' . mysql_error();
            return null;
        }

        if(mysql_num_rows( $query_result ) < 1) {
            return null;
        }

        $accounts = array();


        while($row = mysql_fetch_assoc( $query_result )) {
        
            $account = new Account( 
                $row['uid'],        $row['screen_name'], $row['pwd_md5'], 
                $row['email'],      $row['state'],       $row['group_name'],
                $row['msg_count'],  $row['karma'],       $row['comment'],
                $row['changedate'], $row['createdate'],  $row['act_code']
            );
            array_push( $accounts, $account );

        }

        return $accounts;
    }


    //probability function for events that have to find place in one of $num cases
    private function one_in( $num ) {
        $rand = rand( 1, $num );
        return ($rand == $num)? true : false;            
    }


    // helper for update queries. Returns the SET part.
    private function format_sets( $sets ) {
        $strings = array();

        foreach ($sets as $k => $v) {
            array_push( $strings, "$k=$v" );
        }

        return join( ', ', $strings);
    }

    #### general function for formatting insert query for lot of vals into a table
    #### $tablename is the name of the table
    #### $vals is an associative array with "column => value" mappings
    private function format_insert_query( $tablename, $vals ) {
        $colnames = array();
        $colvals  = array();

        foreach($vals as $cname => $cval ) {
            array_push( $colnames, '`' . $cname . '`');
            array_push( $colvals,  $cval );
        }

        $query = "insert into $tablename (" . 
            join( ',', $colnames) . ") VALUE(" . join(',', $colvals) .")";
        return $query;
    }
    
    private function get_limit( $pagenum, $pagesize ) {
        $start = ($pagenum - 1) * $pagesize;
        return "LIMIT $start, $pagesize";
    }

    ####
    public function dump_info() {
        echo "dbl->dump_info()\n";
        echo "latest query: ", $this->latest_query, "\n";
        echo "mysql error : ", $this->mysql_error, "\n";
    }

}

?>